﻿using DataAccess;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Utility;
using Utity;

namespace TestInterface
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnGenerateDMLSql_Click(object sender, EventArgs e)
        {
                 //git Search
                string tableName = "SWEH_CLVT_CLM_VISIT_INFO";
                DataSet dset = GetTableColumnPropertyList(tableName);
                string spNameText = SetSPHeader(tableName, "INSERT");
                string declareText = SetSpOpenParamValues(dset);
                string allText= SetInsertSql(tableName, dset);
            string all = spNameText + declareText + allText;
                Debug.Write(all);


        }

        string SetInsertSql(string tableName, DataSet dset) {
            if (dset.Tables.Count != 3) {
                Logger.Instance.WriteEventLog("动态生成DML语句", string.Format("{0}表属性获取失败",tableName));
                return "";
            }

            if (dset.Tables[0].Rows.Count==0 )
            {
                Logger.Instance.WriteEventLog("动态生成DML语句", string.Format("{0}表，列字段获取失败",tableName));
                return "" ;
            }
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(string.Format("INSERT INTO [{0}]", tableName));
           
            DataTable dtblTableColumnList = dset.Tables[0];
            DataTable dtblTableProperty = dset.Tables[1];
            DataTable dtblColumnProperty = dset.Tables[2];
            string strFields = "";
            string strParams = "";

            foreach (DataRow row in dtblTableColumnList.Rows)
            {
                string column_name= row["column_name"].ToString();
                string column_type = row["column_type"].ToString();
                bool nullable = (bool)row["nullable"];
                strFields += column_name + ",\r\n";
                strParams += GetSpProcessParamValue(row, dtblColumnProperty) + ",\r\n";

            }

            if (!string.IsNullOrEmpty(strFields) && !string.IsNullOrEmpty(strParams))
            {
                sb.AppendLine("(\r\n" + strFields.TrimEnd(',','\r','\n') + ")");
                sb.AppendLine("VALUES(\r\n" + strParams.TrimEnd(',', '\r', '\n') + ")\r\n");
            }
            string allSql = sb.ToString();
            Logger.Instance.WriteEventLog("动态生成DML语句", string.Format("{0}表，生产结果:{0}", allSql));
            return allSql;
        }

        string SetSPHeader(string tableName,string method)
        {
            string sp_name = "SPEH_" + tableName.ToUpper() + "_" + method.ToUpper();
            return string.Format("ALTER PROC  [dbo].[{0}]\r\n/*************************************************\r\n \t\tCreate by Admin at {1:yyyy-MM-dd}\r\n\t\tDeclare Arguments\r\n*************************************************/\r\n"
                    ,sp_name,DateTime.Now
                );
        }

        string SetSpOpenParamValues(DataSet dset)
        {
            StringBuilder sb = new StringBuilder();
            DataTable dtblTableColumnList = dset.Tables[0];
            foreach (DataRow drowColumnInfo in dtblTableColumnList.Rows)
            {
                string column_name = (string)drowColumnInfo["column_name"];
                string column_type = (string)drowColumnInfo["column_type"];

                Int16 length = (Int16)drowColumnInfo["column_length"];
                bool isKey = (bool)drowColumnInfo["is_key"];

                string ref_column_type = column_type;
                if (new string[] { "varchar", "nvarchar", "text", "ntext" }.Contains(column_type))
                {
                    ref_column_type += "(" + length.ToString() + ")";
                }

                if (isKey)
                {
                    sb.AppendFormat("\t\t{0} {1}=NULL OUTPUT,\r\n", "@p" + column_name, ref_column_type);
                }
                else
                {
                    sb.AppendFormat("\t\t{0} {1}=NULL,\r\n", "@p" + column_name, ref_column_type);
                }   
            }
            sb.AppendFormat("\t\t@pRETURN_CODE  int=0 OUTPUT,\r\n");
            sb.AppendFormat("\t\t@pRETURN_MESSAGE  varchar(255)=NULL	OUTPUT,\r\n");
            sb.AppendFormat("\t\t@pEHUSER  varchar(30)=NULL\r\n");
            return sb.ToString() + "\tAS\r\n";


        }

        string GetSpProcessParamValue(DataRow drowColumnInfo, DataTable dtblColumnProperty)
        {
            string column_name= (string)drowColumnInfo["column_name"], column_type = (string)drowColumnInfo["column_type"];
            bool nullable =  (bool)drowColumnInfo["nullable"];
            Int16 length = (Int16)drowColumnInfo["column_length"];
            string paramName = "@p" + column_name;
            bool isKey= (bool)drowColumnInfo["is_key"];
            if (isKey)
            {
                return paramName;
            }
            
            string paramFormula = "ISNULL({0},{1})";
            string value = "";
            DataRow[] drArrDefaultColumnProperty = dtblColumnProperty.Select(string.Format("[column_name]='{0}' and [name]='默认值'", column_name));
            if (drArrDefaultColumnProperty.Length > 0) {
                DataRow drowDefault = drArrDefaultColumnProperty[0];
                value = drowDefault["value"].ToString();
            }
            if (new string[] { "int", "float","bit" }.Contains(column_type) && string.IsNullOrEmpty(value))
            {
                value = "0";
            }
            else if (new string[] { "money","decimal" }.Contains(column_type) && string.IsNullOrEmpty(value)) {
                value = "0.00";
            }
            else if (new string[] { "varchar", "nvarchar","ntext","text" }.Contains(column_type) && string.IsNullOrEmpty(value)) 
            {
                if (length <= 2)
                {
                    value = "'N'";
                }
                else
                {
                    value = "''";
                }
            }
            else if (column_type=="datetime" && string.IsNullOrEmpty(value))
            {
                value = "'1900-01-01'";
            }

            return string.Format(paramFormula,paramName,value);

        }

        DataSet GetTableColumnPropertyList(string tableName)
        {
            if (string.IsNullOrWhiteSpace(tableName))
            {
                throw new ArgumentNullException("表名变量未设置");
            }
            string filePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "SqlText",  "表字段属性.SQL");
            string sqlText = File.ReadAllText(filePath, System.Text.Encoding.UTF8);
            Dictionary<string, object> paramValues = new ParamterSet().SetValueChain("TABLE_NAME", tableName)
                                                                      .SetValueChain("DB_NAME", "AIA_XC")
                                                                      .GetValues();
            DataSet outputDataSet = new DataSet();
            IDictionary<string,object> returnParams= OperationDA.Instance.ExcuteTextOutputDulpex(sqlText
                                                                                        ,paramValues 
                                                                                        , out outputDataSet);
            return outputDataSet;
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }
    }
}
